USE [DSRSystem]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetKeyESGIssuesByCompanyIds]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetKeyESGIssuesByCompanyIds]
GO

CREATE PROCEDURE [dbo].[spGetKeyESGIssuesByCompanyIds]
	@CompanyIds NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @CompanyIds, 1)

	DECLARE @SelectedCompanies TABLE
	(
		Id uniqueidentifier
	)
	INSERT INTO @SelectedCompanies
	SELECT TID.id.value('.','uniqueidentifier') AS Id
	FROM @XML.nodes('/CompanyIds') AS T(c) outer apply T.c.nodes('Id') as TID(id)
	
	SELECT	CompanyKeyESGIssues.Id, 
			CompanyKeyESGIssues.CompanyId, 
			Companies.Name AS CompanyName,
			CAST(Sectors.Number AS int) AS SubIndustryCode,
			KeyESGIssues.Text AS Name, 
			KeyESGIssues.ExternalId, 
			KeyESGIssues.Description, 
			CompanyKeyESGIssues.Comment, 
			CompanyKeyESGIssues.SortKey
	FROM	dbo.Companies AS Companies
	INNER JOIN @SelectedCompanies AS SelectedCompanies ON SelectedCompanies.Id = Companies.CompanyId
	INNER JOIN dbo.CompanyKeyESGIssues AS CompanyKeyESGIssues ON Companies.CompanyId = CompanyKeyESGIssues.CompanyId AND Companies.WorkingVersion - 1 = CompanyKeyESGIssues.Version 
	INNER JOIN dbo.KeyESGIssues AS KeyESGIssues ON CompanyKeyESGIssues.KeyESGIssueId = KeyESGIssues.Id
	INNER JOIN dbo.Sectors AS Sectors ON Companies.GICSSector = Sectors.SectorId
	ORDER BY Companies.Name, CompanyKeyESGIssues.SortKey

END
GO


